#include "sqlite3_db.h"
using namespace Poco::Data;

const std::string sqlite3_filename = ".database.sqlite";

sql_query::sql_query():sql("SQLite", sqlite3_filename)
{}

void sql_query::sql_insert_trace(std::string date, float displacement_y, float* y, unsigned trace_length)
{

  float displacement_x = 0;

  try
    {
      sql.begin();

      sql << "INSERT INTO trace(date_acquired,displacement_y,displacement_x) VALUES (:date,:dispy,:dispx);",
      use(date),use(displacement_y),use(displacement_x),now;

      Poco::Int64 rowid = 0;
      sql << "SELECT max(ROWID) FROM trace", into(rowid),now;
      std::string zeros;

      for (unsigned i=0; i<trace_length; i++)
        {
          if (i<10) zeros = "000";
          else if (i<100) zeros = "00";
          else if (i<1000) zeros = "0";
          else zeros="";

          sql << "UPDATE trace SET tr" << zeros << i << "=:tr WHERE ROWID=:rowid;",use(y[i]), use(rowid),now;
        }
      sql.commit();
    }
  catch (...)
    {
      throw("Warning: Cannot backup single trace to SQL database.");
    }

}

void sql_query::sql_insert_description(std::string date, std::string description,
                                 float start_wavelength, float stop_wavelength,
                                 float sensitivity, float resolution_bandwidth,unsigned trace_no)
{
  Poco::Int64 osa_id = save_osa_setting(start_wavelength, stop_wavelength,sensitivity, resolution_bandwidth,trace_no);

  try
    {
      sql << "INSERT INTO image(date_acquired,osa_id,description) VALUES (:date,:osa_id,:description);", use(date), use(osa_id), use(description),now;
    }
  catch (...)
    {
      throw("Warning: Cannot backup image descriptions to SQL database.");
    }
}

long long sql_query::save_osa_setting(float start_wavelength, float stop_wavelength,
    float sensitivity, float resolution_bandwidth,unsigned trace_no)
{
  Poco::Int64 id=0;
  unsigned count=0;

  try
    {
      sql << "SELECT count(*) FROM osa_setting WHERE start_wavelength=:startwav AND stop_wavelength=:stopwav AND sensitivity=:sens AND resolution_bandwidth=:resbw AND trace_no=:traceno;",
      use(start_wavelength), use(stop_wavelength), use(sensitivity), use(resolution_bandwidth), use(trace_no), into(count), now;

      if (count==0)
        {
          sql << "INSERT INTO osa_setting(start_wavelength,stop_wavelength,sensitivity,resolution_bandwidth,trace_no) VALUES (:startwav,:stopwav,:sens,:resbw,:traceno);",
          use(start_wavelength), use(stop_wavelength), use(sensitivity), use(resolution_bandwidth), use(trace_no), now;
        }

      sql << "SELECT id FROM osa_setting WHERE start_wavelength=:startwav AND stop_wavelength=:stopwav AND sensitivity=:sens AND resolution_bandwidth=:resbw AND trace_no=:traceno;",
      use(start_wavelength), use(stop_wavelength), use(sensitivity), use(resolution_bandwidth), use(trace_no), into(id), now;
    }
  catch (...)
    {
      throw("Warning: Cannot backup OSA settings to SQL database.");
    }
  return id;
}
